package com.liu.mysqldemo.repository;

import com.liu.mysqldemo.entity.Teacher;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface TeacherRepository extends JpaRepository<Teacher,Integer> {

    //自定义查询
    List<Teacher> findTeacherByNameIsLike(String name);
    //自定义添加排序、分页
    List<Teacher> findTeacherByNameIsLike(String name, Pageable pageable);

    List<Teacher> findTeachersByAgeGreaterThan(int age);

    //自定义sql语句查询--单表分页
    @Query(value="select id,name from teacher where id > (:id)",nativeQuery = true)
    Page<Map<String,Object>> findById(@Param("id") Integer id, Pageable pageable);

    //自定义sql语句查询--联合查询(采用hql方式来做查询)
    @Query(value="SELECT t.id as id,t.name as tname,g.name as gname FROM Teacher t LEFT JOIN Grade g on t.gradeId = g.id where t.id < (:id)")
    Page<Map<String,Object>> findUnionById(@Param("id") Integer id, Pageable pageable);

    //分页联合查询---正常(原生sql方式)
    @Query(value ="select t.id,t.name,g.name as gname from Teacher as t left join Grade as g on t.grade_id = g.id where t.id < (:id)",nativeQuery = true)
    List<Map<String,Object>> findTeachersById(@Param("id") Integer id, Pageable pageable);

    //原生分页查询
    @Query(value ="select t.id,t.name,g.name as gname from Teacher as t left join Grade as g on t.grade_id = g.id where t.id < (:id)",
            countQuery = "select count(*) from Teacher as t left join Grade as g on t.grade_id = g.id where t.id < (:id) ",
            nativeQuery = true)
    Page<Map<String,Object>> findTeachersByIdwithPage(@Param("id") Integer id, Pageable pageable);
}
